# -*- coding:utf-8 -*-  
import System,clr,json;
clr.AddReference("System.Data");
from System.Data import DataSet,DataTable,ConnectionState,CommandType,CommandBehavior,SqlDbType,ParameterDirection;
from System.Data.SqlClient import SqlConnection,SqlCommand,SqlDataAdapter,SqlParameter ;
_connString= "Data Source=139.196.108.200;Initial Catalog=GLOBAL;Uid=sa;password=Malocl321;";
_conn= SqlConnection(_connString);


def getConnection():
    if (_conn.State == ConnectionState.Closed):  
            _conn.Open();  
    return _conn;

def query(cmdText,cmdType="",dictParamter={}):
    dt = DataTable();
    cmdText= cmdText.strip();
    cmd = SqlCommand(cmdText,getConnection());
    cmd.CommandType= CommandType.StoredProcedure;
    if not cmdType:
         cmd.CommandType= CommandType.Text;
    addCommandParamters(cmd,dictParamter);
    adapter = SqlDataAdapter(cmd);
    dt = DataTable();
    adapter.Fill(dt);
    return dt;
	
def parseDict(dtbl):
	if not isinstance(dtbl,System.Data.DataTable): return None;
	lst= [];
	dict= None;
	for row in dtbl.Rows:
		dict= {};
		for col in dtbl.Columns:
			dict[col.ColumnName]= row[col].ToString();
		lst.append(dict);
	return lst;

def parseJSON(dtbl):
	lst= parseDict(dtbl);
	if lst:return json.dumps(lst,ensure_ascii=False);
	return;


def getSPParamList(procedure_name):
	cmd = SqlCommand("exec sp_sproc_columns @procedure_name='"+ procedure_name +"'",getConnection());
	cmd.CommandType= CommandType.Text;
	adapter= SqlDataAdapter(cmd);
	dt= DataTable();
	adapter.Fill(dt);
	return dt;

def addCommandParamters(cmd,dictParamter):
    _sppPrex= "@p";
    if cmd.CommandType== CommandType.StoredProcedure:
        dtblParamters= getSPParamList(cmd.CommandText);
        for drowParamter in dtblParamters.Rows:
            columnName= drowParamter["COLUMN_NAME"].ToString().lstrip(_sppPrex);
            i_or_o=  int(drowParamter["COLUMN_TYPE"].ToString());
            if i_or_o == 2 or dictParamter.has_key(columnName):
                cmdParamter= SqlParameter(_sppPrex+ columnName,SqlDbType.VarChar,255);
                if i_or_o == 2:
                    cmdParamter.Direction= ParameterDirection.Output;
                if dictParamter.has_key(columnName):
                    cmdParamter.Value= dictParamter[columnName];
                cmd.Parameters.Add(cmdParamter);    
    else:
        for key in dictParamter.keys():
            cmd.Parameters.Add(_sppPrex + key,dictParamter[key]); 
    return;
	
	
	


